package com.yellowpage.nsp.service;

import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.List;
import java.util.Map;

import org.springframework.transaction.TransactionDefinition;
import org.springframework.transaction.TransactionStatus;
import org.springframework.transaction.support.DefaultTransactionDefinition;

import com.yellowpage.white.common.Constants;
import com.yellowpage.white.common.PageRender;
import com.yellowpage.white.dao.QueryAssistantSQL;
import com.yellowpage.white.service.BaseServiceImpl;
import com.yellowpage.nsp.data.NspLogSum;
import com.yellowpage.nsp.dao.NspLogSumDao;

public class NspLogSumServiceImpl extends BaseServiceImpl implements
		NspLogSumService {
	private NspLogSumDao nspLogSumDao;

	public void setNspLogSumDao(NspLogSumDao nspLogSumDao) {
		this.nspLogSumDao = nspLogSumDao;
	}

	/* Smooth generate Method */
	public NspLogSum getNspLogSum(Long id) {
		return nspLogSumDao.get(id);
	}

	public List<NspLogSum> getAllNspLogSum() {
		return nspLogSumDao.getAll();
	}

	public List<Map> queryNspLogSumByPage(Map queryMap, PageRender pageRender) {

		QueryAssistantSQL sqlAssistant = baseDao
				.newQueryAssistantSQL(
						"SELECT a.LOG_SUM_ID , a.SERVER_ID , a.LOG_FILE_NAME , a.LOG_TIME , a.REQUEST_TYPE , a.REQUEST_CONTENT , a.RESPONSE_CONTENT , a.CREATE_USER , a.CREATE_TIME , a.NOTE , a.RESPONSE_CODE , a.COUNT , a.STATUS    FROM NSP_LOG_SUM a WHERE 1=1 ")
				.addCondition(" and LOG_SUM_ID = :logSumId ")
				.addCondition(" and SERVER_ID = :serverId ")
				.addCondition(" and LOG_FILE_NAME = :logFileName ")
				.addCondition(" and LOG_TIME = :logTime ")
				.addCondition(" and REQUEST_TYPE = :requestType ")
				.addCondition(" and REQUEST_CONTENT = :requestContent ")
				.addCondition(" and RESPONSE_CONTENT = :responseContent ")
				.addCondition(" and CREATE_USER = :createUser ")
				.addCondition(
						" and date_format(CREATE_TIME,'%Y-%m-%d') =  :createTime  ")
				.addCondition(" and NOTE = :note ")
				.addCondition(" and RESPONSE_CODE = :responseCode ")
				.addCondition(" and COUNT = :count ")
				.addCondition(" and STATUS = :status ");

		List<Map> resultList = sqlAssistant.queryByPage(
				baseDao.getConnection(), queryMap, pageRender);

		return resultList;
	}

	
	public List<Map> queryNspLogSumTellDay(Map queryMap){
		  
		QueryAssistantSQL sqlAssistant = baseDao.newQueryAssistantSQL(
				"select b.REQUEST_TYPE,b.LOG_DATE, a.COMPANY, a.APP_NAME, a.TYPE, a.STATUS,b.REQUEST_COUNT,b.REQUEST_VALID_IMSI_TEL,b.REQUEST_VALID_IMSI,"+
				" 		b.REQUEST_VALID_TEL,b.REQUEST_INVALID_IMSI_TEL,b.REQUEST_NO_IMSI_TEL,b.DAY_VALID_IMSI_TEL,b.DAY_VALID_IMSI,b.DAY_VALID_TEL,"+
				"		b.ALL_VALID_IMSI_TEL,b.ALL_VALID_IMSI,b.ALL_VALID_TEL," +
				"		b.REQUEST_COUNT as REQCOUNT,"+
				"		b.REQUEST_VALID_IMSI_TEL as REQIMSITEL,"+
				"		b.REQUEST_VALID_IMSI as REQIMSI,"+
				"		b.REQUEST_VALID_TEL as REQTEL,"+
				"		b.REQUEST_INVALID_IMSI_TEL as REQERRORIMSITEL,"+
				"		b.REQUEST_NO_IMSI_TEL as REQNOIMSITEL,"+		
				" 		b.DAY_VALID_IMSI_TEL as DAYIMSITEL ,"+
				" 		b.DAY_VALID_IMSI as DAYIMSI,"+
				" 		b.DAY_VALID_TEL	as DAYTEL,"+
				"       (b.DAY_VALID_IMSI_TEL+b.DAY_VALID_IMSI+b.DAY_VALID_TEL) as DAYTELLALL,  " +
				" 		b.ALL_VALID_IMSI_TEL as ALLIMSITEL,"+
				" 		b.ALL_VALID_IMSI as ALLIMSI,"+
				" 		b.ALL_VALID_TEL as ALLTEL,"+
				"       (b.ALL_VALID_IMSI_TEL+b.ALL_VALID_IMSI+b.ALL_VALID_TEL) as ALLTOTAL, " +
				"       (SELECT (MAX(m.ALL_VALID_IMSI_TEL)) from  NSP_LOG_TEL_SUM AS m WHERE m.LOG_DATE=b.LOG_DATE AND m.REQUEST_TYPE=b.REQUEST_TYPE)-(SELECT (MAX(f.ALL_VALID_IMSI_TEL)) FROM NSP_LOG_TEL_SUM as f WHERE f.LOG_DATE = DATE_SUB(b.LOG_DATE, INTERVAL 1 DAY) AND f.REQUEST_TYPE = b.REQUEST_TYPE) AS INCREMENTIMSITEL, " +
				"       (SELECT (MAX(m.ALL_VALID_IMSI)) from  NSP_LOG_TEL_SUM AS m WHERE m.LOG_DATE=b.LOG_DATE AND m.REQUEST_TYPE=b.REQUEST_TYPE)-(SELECT (MAX(f.ALL_VALID_IMSI)) FROM NSP_LOG_TEL_SUM as f WHERE f.LOG_DATE = DATE_SUB(b.LOG_DATE, INTERVAL 1 DAY) AND f.REQUEST_TYPE=b.REQUEST_TYPE) AS INCREMENTIMSI , " +
				"		(SELECT (MAX(m.ALL_VALID_TEL)) from  NSP_LOG_TEL_SUM AS m WHERE m.LOG_DATE=b.LOG_DATE AND m.REQUEST_TYPE=b.REQUEST_TYPE)-(SELECT (MAX(f.ALL_VALID_TEL)) FROM NSP_LOG_TEL_SUM as f WHERE f.LOG_DATE = DATE_SUB(b.LOG_DATE, INTERVAL 1 DAY) AND f.REQUEST_TYPE = b.REQUEST_TYPE) AS INCREMENTTEL,"+
				"       (SELECT (MAX(m.ALL_VALID_IMSI_TEL)+MAX(m.ALL_VALID_IMSI)+MAX(m.ALL_VALID_TEL)) from  NSP_LOG_TEL_SUM AS m WHERE m.LOG_DATE=b.LOG_DATE AND m.REQUEST_TYPE=b.REQUEST_TYPE)-(SELECT (MAX(f.ALL_VALID_IMSI_TEL)+MAX(f.ALL_VALID_IMSI)+MAX(f.ALL_VALID_TEL)) FROM NSP_LOG_TEL_SUM as f WHERE f.LOG_DATE = DATE_SUB(b.LOG_DATE, INTERVAL 1 DAY) AND f.REQUEST_TYPE= b.REQUEST_TYPE) AS INCREMENTTOTAL  " +
				"  from NSP_LOG_TEL_SUM b left JOIN  NSP_REQUEST_TYPE a ON b.REQUEST_TYPE = a.CODE " +
				" where 1=1 ")
				    						.addCondition(" and SUBSTR(b.LOG_DATE FROM 1 FOR 10) >= :requestTimeStart ")
				    						.addCondition(" and SUBSTR(b.LOG_DATE FROM 1 FOR 10) <= :requestTimeEnd ")
							    			.addCondition(" and b.REQUEST_TYPE = :requestType "); 		    
		    

		sqlAssistant.setTail(" group by b.LOG_DATE , b.REQUEST_TYPE ");
		
		List<Map> resultList = sqlAssistant.query(
				baseDao.getConnection(), queryMap);

		return resultList;
		

	}
	
	public List<Map> queryNspLogSumTellDayJFreeChart(Map queryMap){
		  
		QueryAssistantSQL sqlAssistant = baseDao.newQueryAssistantSQL(
						"select ,b.LOG_DATE, a.COMPANY, b.REQUEST_COUNT as REQCOUNT,"
								+" b.ALL_VALID_IMSI_TEL as ALLIMSITEL,"
								+" (SELECT (MAX(m.ALL_VALID_IMSI_TEL)) from  NSP_LOG_TEL_SUM AS m WHERE m.LOG_DATE=b.LOG_DATE AND m.REQUEST_TYPE=b.REQUEST_TYPE)-(SELECT (MAX(f.ALL_VALID_IMSI_TEL)) FROM NSP_LOG_TEL_SUM as f WHERE f.LOG_DATE = DATE_SUB(b.LOG_DATE, INTERVAL 1 DAY) AND f.REQUEST_TYPE = b.REQUEST_TYPE) AS INCREMENTIMSITEL" 
								+ "  from NSP_LOG_TEL_SUM b left JOIN  NSP_REQUEST_TYPE a ON b.REQUEST_TYPE = a.CODE "
								+ " where 1=1 ")
				    						.addCondition(" and SUBSTR(b.LOG_DATE FROM 1 FOR 10) >= :requestTimeStart ")
				    						.addCondition(" and SUBSTR(b.LOG_DATE FROM 1 FOR 10) <= :requestTimeEnd ")
							    			.addCondition(" and b.REQUEST_TYPE = :requestType "); 		    

		sqlAssistant.setTail(" group by b.LOG_DATE , b.REQUEST_TYPE ");
		List<Map> resultList = sqlAssistant.query(baseDao.getConnection(),
				queryMap);
		return resultList;		
	}
	
	
	public List<Map> queryNspLogSumTellMonth(Map queryMap){
		
		QueryAssistantSQL sqlAssistant = baseDao.newQueryAssistantSQL(
				"select b.REQUEST_TYPE,SUBSTR(b.LOG_DATE FROM 1 FOR 7) as LOG_DATE, a.COMPANY, a.APP_NAME, a.TYPE, "+
				"		SUM(b.REQUEST_COUNT) as REQCOUNT,"+
				"		SUM(b.REQUEST_VALID_IMSI_TEL) as REQIMSITEL,"+
				"		SUM(b.REQUEST_VALID_IMSI) as REQIMSI,"+
				"		SUM(b.REQUEST_VALID_TEL) as REQTEL,"+
				"		SUM(b.REQUEST_INVALID_IMSI_TEL) as REQERRORIMSITEL,"+
				"		SUM(b.REQUEST_NO_IMSI_TEL) as REQNOIMSITEL,"+		
				" 		SUM(b.DAY_VALID_IMSI_TEL) as DAYIMSITEL , FORMAT((SUM(b.DAY_VALID_IMSI_TEL)/SUBSTR(last_day(b.LOG_DATE) FROM 9 FOR 2)),2) as DAYIMSITELAVERAGE ,"+
				" 		SUM(b.DAY_VALID_IMSI) as DAYIMSI, FORMAT((SUM(b.DAY_VALID_IMSI)/SUBSTR(last_day(b.LOG_DATE) FROM 9 FOR 2)),2) as DAYIMSIAVERAGE,"+
				" 		SUM(b.DAY_VALID_TEL) as DAYTEL,FORMAT((SUM(b.DAY_VALID_TEL)/SUBSTR(last_day(b.LOG_DATE) FROM 9 FOR 2)),2)as DAYTELAVERAGE,"+
				"       (SUM(b.DAY_VALID_IMSI_TEL)+SUM(b.DAY_VALID_IMSI)+SUM(b.DAY_VALID_TEL)) as DAYTELLALL,  " +
				" 		MAX(b.ALL_VALID_IMSI_TEL) as ALLIMSITEL,"+
				" 		MAX(b.ALL_VALID_IMSI) as ALLIMSI,"+
				" 		MAX(b.ALL_VALID_TEL) as ALLTEL,"+
				"       (MAX(b.ALL_VALID_IMSI_TEL)+MAX(b.ALL_VALID_IMSI)+MAX(b.ALL_VALID_TEL)) as ALLTOTAL, " +
				"       (SELECT (MAX(m.ALL_VALID_IMSI_TEL)) from  NSP_LOG_TEL_SUM AS m WHERE SUBSTR(m.LOG_DATE FROM 1 FOR 7)=SUBSTR(b.LOG_DATE FROM 1 FOR 7) AND m.REQUEST_TYPE=b.REQUEST_TYPE)-(SELECT (MAX(f.ALL_VALID_IMSI_TEL)) FROM NSP_LOG_TEL_SUM as f WHERE SUBSTR(f.LOG_DATE FROM 1 FOR 7)=SUBSTR(DATE_SUB(b.LOG_DATE, INTERVAL 1 MONTH) FROM 1 FOR 7) AND f.REQUEST_TYPE = b.REQUEST_TYPE) AS INCREMENTIMSITEL,  " +
				"       (SELECT (MAX(m.ALL_VALID_IMSI)) from  NSP_LOG_TEL_SUM AS m WHERE SUBSTR(m.LOG_DATE FROM 1 FOR 7)=SUBSTR(b.LOG_DATE FROM 1 FOR 7) AND m.REQUEST_TYPE=b.REQUEST_TYPE)-(SELECT (MAX(f.ALL_VALID_IMSI)) FROM NSP_LOG_TEL_SUM as f WHERE SUBSTR(f.LOG_DATE FROM 1 FOR 7)=SUBSTR(DATE_SUB(b.LOG_DATE, INTERVAL 1 MONTH) FROM 1 FOR 7) and f.REQUEST_TYPE=b.REQUEST_TYPE) AS INCREMENTIMSI , " +
				"		(SELECT (MAX(m.ALL_VALID_TEL)) from  NSP_LOG_TEL_SUM AS m WHERE SUBSTR(m.LOG_DATE FROM 1 FOR 7)=SUBSTR(b.LOG_DATE FROM 1 FOR 7) AND m.REQUEST_TYPE=b.REQUEST_TYPE)-(SELECT (MAX(f.ALL_VALID_TEL)) FROM NSP_LOG_TEL_SUM as f WHERE SUBSTR(f.LOG_DATE FROM 1 FOR 7)=SUBSTR(DATE_SUB(b.LOG_DATE, INTERVAL 1 MONTH) FROM 1 FOR 7) AND f.REQUEST_TYPE = b.REQUEST_TYPE) AS INCREMENTTEL,"+
				"       (SELECT (MAX(m.ALL_VALID_IMSI_TEL)+MAX(m.ALL_VALID_IMSI)+MAX(m.ALL_VALID_TEL)) from  NSP_LOG_TEL_SUM AS m WHERE SUBSTR(m.LOG_DATE FROM 1 FOR 7)=SUBSTR(b.LOG_DATE FROM 1 FOR 7) AND m.REQUEST_TYPE=b.REQUEST_TYPE)-(SELECT (MAX(f.ALL_VALID_IMSI_TEL)+MAX(f.ALL_VALID_IMSI)+MAX(f.ALL_VALID_TEL)) FROM NSP_LOG_TEL_SUM as f WHERE SUBSTR(f.LOG_DATE FROM 1 FOR 7)=SUBSTR(DATE_SUB(b.LOG_DATE, INTERVAL 1 MONTH) FROM 1 FOR 7) AND f.REQUEST_TYPE= b.REQUEST_TYPE) AS INCREMENTTOTAL  " +
				"  from NSP_LOG_TEL_SUM b left JOIN  NSP_REQUEST_TYPE a ON b.REQUEST_TYPE = a.CODE " +
				" where 1=1 ")
				    						.addCondition(" and SUBSTR(b.LOG_DATE FROM 1 FOR 4) >= :yearStart ")
				    						.addCondition(" and SUBSTR(b.LOG_DATE FROM 6 FOR 2) >= :monthStart ")
				    							.addCondition(" and SUBSTR(b.LOG_DATE FROM 1 FOR 4) <= :yearEnd ")
				    						.addCondition(" and SUBSTR(b.LOG_DATE FROM 6 FOR 2) <= :monthEnd ")
							    			.addCondition(" and b.REQUEST_TYPE = :requestType "); 		    
		    

		sqlAssistant.setTail(" group by SUBSTR(b.LOG_DATE FROM 1 FOR 7) , b.REQUEST_TYPE ");
		
		List<Map> resultList = sqlAssistant.query(
				baseDao.getConnection(), queryMap);

		return resultList;
		
		
	}
	
	
	
	
	
	
	public void saveNspLogSum(NspLogSum nspLogSum) {
		nspLogSumDao.save(nspLogSum);
	}

	public void updateNspLogSum(NspLogSum nspLogSum) {
		nspLogSumDao.update(nspLogSum);
	}

	public void delete(List deleteList) {
		nspLogSumDao.remove(deleteList);
	}

}